﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace CounterServerModel
{
    /// <summary>
    /// A class that manipulates the database.
    /// </summary>
    public class DBService
    {
        private string m_ConnectionString;
        private SqlConnection m_SqlConnection;
        private SqlCommand m_SqlCommand;
        private SqlDataAdapter m_SqlDataAdapter;

        private static DBService m_Instance = null;
        private static object syncObject = new Object();

        public static DBService Instance
        {
            get 
            {
                if (m_Instance == null) 
                {
                    lock (syncObject)
                    {
                        if (m_Instance == null)
                            m_Instance = new DBService();
                    }
                }

                return m_Instance;
            }
        }

        private DBService()
        {
            Configurator configurator = new Configurator();
            m_ConnectionString = string.Format(@"Data Source='{0}';Initial Catalog='{1}';User Id='{2}';Password='{3}';", configurator.GetSQLServerAddress(), configurator.GetDBName(), configurator.GetAppUserName(), configurator.GetAppPassword());
        }

        public DataTable PerformQuery(string i_SelectCommand)
        {
            m_SqlConnection = new SqlConnection(m_ConnectionString);
            m_SqlCommand = new SqlCommand();
            m_SqlCommand.Connection = m_SqlConnection;
            m_SqlDataAdapter = new SqlDataAdapter();
            m_SqlDataAdapter.SelectCommand = new SqlCommand();
            m_SqlDataAdapter.SelectCommand.Connection = m_SqlConnection;

            DataTable table = new DataTable();

            try
            {
                m_SqlConnection.Open();
                m_SqlDataAdapter.SelectCommand.CommandText = i_SelectCommand;
                m_SqlDataAdapter.Fill(table);
            }
            finally
            {
                m_SqlConnection.Close();
            }

            return table;
        }

        public void ExecuteCommand(string i_Command)
        {
            m_SqlConnection = new SqlConnection(m_ConnectionString);
            m_SqlCommand = new SqlCommand();
            m_SqlCommand.Connection = m_SqlConnection;
            m_SqlDataAdapter = new SqlDataAdapter();
            m_SqlDataAdapter.SelectCommand = new SqlCommand();
            m_SqlDataAdapter.SelectCommand.Connection = m_SqlConnection;

            m_SqlCommand.CommandText = i_Command;

            try
            {
                m_SqlConnection.Open();
                m_SqlCommand.ExecuteNonQuery();
            }
            finally
            {
                m_SqlConnection.Close();
            }
        }
    }
}
